import os
import sqlite3 as db
from lib.MySequence import MySequence
import xlrd
import datetime

class SQL(MySequence):
    """
        SQL 说明
            mySave
                in           table = 'str1' datas = {}
                out         成功：1 失败：0
            myDelect
                in          table='str1', conditions={}
                out         成功：1 失败：0
            myUpdate
                in          table='str1', datas={}, conditions={}
                out         成功：1 失败：0
            myFetch
                in          table=[], columns=[], conditions={}
                out         成功：结果集 失败：0
            currentTime(self):
                得到当前时间
            importExcelDate(self, table1 = 'importTable', importExcel = 'excelLocaltion', tableDict = [])
                导入excel数据
    """
    def __init__(self, path):
        super().__init__(path)

    def mySave(self, table='str1', datas={}):
        '''
        in
            table=str1, datas={}
        out
            True or False
        传入参数为 需要insert数据的table，和数据。数据为dict 类型{'key1': data1, 'key2':data2}
        最终形成的sql 为 
            insert into table (key1,key2) values (data1, data2)
        '''
        if len(datas):
            columns = ','.join(datas.keys())
            values = ','.join(':%s' %k for k in datas.keys())
            insertSQL='insert into %s (%s) values(%s)' % (table, columns, values)
            try:
                self.cu.execute(insertSQL,datas)
                self.conn.commit()
                return 1
            except Exception as ex:
                raise ex
                return 0
        else:
            print("输入不能为空，请从新输入值")
            return 0

    def myDelete(self, table='str1', conditions={}):
        '''
    in
        table=str1, condition={}
    out
        成功 1， 失败 0
    table=str, condition={}
    传入参数为 需要fetch data数据的table，和 fetch 条件。条件为dict 类型 condition {'key1':data1, 'key2':data2}

        delete from table where key1 = data1 and key2 and data2
        '''
        condition = ' and '.join(('%s=:%s')  %(k ,k) for k in conditions.keys())
        deleteSQL = 'delete from %s where %s' % (table, condition)
        try:
            self.cu.execute(deleteSQL, conditions)
            self.conn.commit()
            return 1
        except Exception as ex:
            raise ex
        

    def myUpdate(self, table='str1', datas={}, conditions={}):
        '''
    in
        table=str1, datas={}, conditions={}
    out
        True or False
    传入参数为 需要update数据的table，和updated 条件。条件为dict 类型data {'key11': data11, 'key22':data22} condition {'key1':data1, 'key2':data2}
        update table set key1 = data1 ,key2 =date2 where key11 = data11 and key22 = data22
        '''
        setDtas = ' , '.join(('%s=:%s')  %(k ,k) for k in datas.keys())
        columnsANDvalues = ' and '.join(('%s=:%s')  %(k ,k) for k in conditions.keys())
        updateSQL = 'update %s set  %s where %s' % (table, setDtas,columnsANDvalues)
        datasANDConditions = datas
        datasANDConditions.update(conditions)
        try:
            self.cu.execute(updateSQL, datasANDConditions)
            self.conn.commit()
            return 1
        except Exception as ex:
            raise ex

    def myFetch(self, table=[], columns=[], conditions={}):
        '''
    in
        table=[], columns=[], conditions={}
    out
        结果集
    传入参数为 需要fetch data数据的table，和 fetch 条件。条件为dict 类型 conditions {'key1':data1, 'key2':data2}

    如果 columns 为空 columns = *
        select * from table where key1 = data1 and key2 = data2
    else
        select columns[0], .. , columns[n] from table where key1 = data1 and key2 = data2

        使用默认的查找方式 select是按建表的顺序返回
        使用conn.row_factory = sqlite3.Row 是方式可以返回 dict 类型的数据
        '''
        if len(columns):
            columns1 = ' , '.join(columns)
        else:
            columns1 = '*'
        columnsANDvalues = ' and '.join(('%s=:%s')  %(k ,k) for k in conditions.keys())
        tables = ' , '.join(table)
        if len(conditions):
            selectSQL= 'select %s from %s where %s' %(columns1, tables, columnsANDvalues)
        else:
            selectSQL = 'select %s from %s' % (columns1, tables)
        try:
            self.cu.execute(selectSQL, conditions)
            return self.cu.fetchall()
        except Exception as ex:
            raise ex
            return 0

    def  currentTime(self):
        return datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    def importExcelDate(self, table1 = 'importTable', importExcel = 'excelLocaltion', tableDict = []):
        '''
    导入excel数据
        '''
        data = xlrd.open_workbook(importExcel)
        excelTable = data.sheet_by_name(table1)
        startRowNum=3

        # dict 是无序的，无法顺序输出
        for a1 in range(startRowNum-1, excelTable.nrows):
            tableRow = excelTable.row_values(a1)
            tableRow_keys = excelTable.row_values(0)
            for b1 in range(excelTable.ncols-1):
                tableDict[tableRow_keys[b1]] = tableRow[b1]
            try:
                self.mySave(table = table1, datas = tableDict)
            except Exception as ex:
                raise ex
        return 1
                




